package com.pub.dbtools;

/**
 * Title: SQL语句操作相关静态方法(操作hive数据库)
 * Desc:
 */

import org.jeecgframework.core.util.PropertiesUtil;

import java.net.ProxySelector;
import java.sql.*;
import java.util.ArrayList;


public class DBTools4Hive {

	/**
	 * 取得数据库连接
	 * 
	 *            Connect 连接名称
	 */
	public static final Connection getConnect(String dbname) {
		try {
			PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
			String driver = tmpvo.readProperty("driverName");
			String currentweb = "tomcat";
			String url = tmpvo.readProperty( "connectionURL");
			String user = tmpvo.readProperty( "username");
			String password = tmpvo.readProperty( "password");

				Class.forName(driver);
				if (currentweb.equalsIgnoreCase("websphere")) {
					ProxySelector ps = ProxySelector.getDefault();
					ProxySelector.setDefault(null);
					Connection con = DriverManager.getConnection(url+"/"+dbname, user,
							password);
					ProxySelector.setDefault(ps);
					return con;
				} else {
					Connection con = DriverManager.getConnection(url+"/"+dbname, user,
							password);
					return con;
				}
		} catch (Throwable t) {
			t.printStackTrace();
			return null;
		} finally {
		}
	}

	/**
	 * 执行表DML语句操作
	 * 
	 * @String DML语句
	 * @Connect 表连接
	 * @Return String ok/error
	 */
	public static final String execDML(String strSql) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");

		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		String tableName = "";
		try {
			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase().trim();

			if (strTemp.startsWith("create") || strTemp.startsWith("drop")
					|| strTemp.startsWith("alter")
					|| strTemp.startsWith("delete")
					|| strTemp.startsWith("insert")
					|| strTemp.startsWith("comment")
					|| strTemp.startsWith("update")) {

				tableName = strTemp;
				stmtDB.executeUpdate(strSql);
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "ok";
			} else {
				System.err.println("not dml format!");
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			con = null;
			return e.toString();
		} finally {
			stmtDB = null;
			rsetTemp = null;
			con = null;
		}
	}

	/**
	 * 执行数据表 增删改SQL 事务
	 * 
	 *            执行操作语句组
	 * @return String ok--成功 error--未成功
	 */
	public static final String execSql(ArrayList sqls) {
		if (sqls.size() < 1) {
			return "ok";
		}

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;


		Connection con = getConnect(dbname);
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
				if (debugsign.toLowerCase().equals("yes")) {
					System.err.println((String) sqls.get(i));
				}
				strSql = ((String) sqls.get(i)).trim();

				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSql(String str) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			}
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作 补充
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "zero" 影响0条记录 "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSqlComple(String str) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;
		Connection con = getConnect(dbname);
		int results = 0;

		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			}
			results = stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			if (results == 0) {
				return "zero";
			} else {
				return "ok";
			}
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作(带数据库名称)
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSql(String dbname, String str) {
		Statement stmtDB = null;
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String debugsign = tmpvo.readProperty("debugsign");
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			}
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改SQL 事务
	 * 
	 * @return String ok--成功 error--未成功
	 */
	public static final String execSql(String dbname, ArrayList sqls) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String debugsign = tmpvo.readProperty("debugsign");
		Statement stmtDB = null;

		if (sqls.size() < 1) {
			return "ok";
		}
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
				if (debugsign.toLowerCase().equals("yes")) {
					System.err.println((String) sqls.get(i));
				}
				strSql = (String) sqls.get(i);
				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSqlDb(String str,
			javax.servlet.http.HttpServletRequest request) {
		return execSql(str);
	}

	/**
	 * 执行数据表 增删改 批量操作
	 * 
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSqlDb(ArrayList tmparr,
			javax.servlet.http.HttpServletRequest request) {
		return execSql(tmparr);
	}
}
